Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(2).偏ったテーブルデータ
当エントリは先日投稿したシリーズ『『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』手順実践』の第2弾です。課題#2の『偏ったテーブルデータ』について内容を見て行きたいと思います。
『課題 #2: 偏ったテーブルデータ』を実践してみる
細かい解説等は参照元エントリを読むなどして頂ければと思いますので割愛します。良い分散キーの特徴は以下のようなものとなり、以降で紹介するSQL文ではその辺りの情報が取得出来る形となります。
- 『高いカーディナリティ』
- 『正規分布/低い偏り』
- 『頻繁にJOINされる』
該当するSQLファイル『table_inspector.sql』は以下となります。一時テーブルに必要な情報を集約し、内容が確認出来る形になっているようですね。"一時テーブル"とは現在のセッション内でのみ表示可能な一時テーブルを作成する機能です。
/*
Table Skew Inspector. Please see http://docs.aws.amazon.com/redshift/latest/dg/c_analyzing-table-design.html
for more information.
Notes:
History:
2015-11-26 meyersi created
*/
DROP TABLE IF EXISTS temp_staging_tables_1;
DROP TABLE IF EXISTS temp_staging_tables_2;
DROP TABLE IF EXISTS temp_tables_report;
CREATE TEMP TABLE temp_staging_tables_1
(schemaname TEXT,
tablename TEXT,
tableid BIGINT,
size_in_megabytes BIGINT);
INSERT INTO temp_staging_tables_1
SELECT n.nspname, c.relname, c.oid,
(SELECT COUNT(*) FROM STV_BLOCKLIST b WHERE b.tbl = c.oid)
FROM pg_namespace n, pg_class c
WHERE n.oid = c.relnamespace
AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
AND c.relname <> 'temp_staging_tables_1';
CREATE TEMP TABLE temp_staging_tables_2
(tableid BIGINT,
min_blocks_per_slice BIGINT,
max_blocks_per_slice BIGINT,
slice_count BIGINT);
INSERT INTO temp_staging_tables_2
SELECT tableid, MIN(c), MAX(c), COUNT(DISTINCT slice)
FROM (SELECT t.tableid, slice, COUNT(*) AS c
FROM temp_staging_tables_1 t, STV_BLOCKLIST b
WHERE t.tableid = b.tbl
GROUP BY t.tableid, slice)
GROUP BY tableid;
CREATE TEMP TABLE temp_tables_report
(schemaname TEXT,
tablename TEXT,
tableid BIGINT,
size_in_mb BIGINT,
has_dist_key INT,
has_sort_key INT,
has_col_encoding INT,
pct_skew_across_slices FLOAT,
pct_slices_populated FLOAT);
INSERT INTO temp_tables_report
SELECT t1.*,
CASE WHEN EXISTS (SELECT *
FROM pg_attribute a
WHERE t1.tableid = a.attrelid
AND a.attnum > 0
AND NOT a.attisdropped
AND a.attisdistkey = 't')
THEN 1 ELSE 0 END,
CASE WHEN EXISTS (SELECT *
FROM pg_attribute a
WHERE t1.tableid = a.attrelid
AND a.attnum > 0
AND NOT a.attisdropped
AND a.attsortkeyord > 0)
THEN 1 ELSE 0 END,
CASE WHEN EXISTS (SELECT *
FROM pg_attribute a
WHERE t1.tableid = a.attrelid
AND a.attnum > 0
AND NOT a.attisdropped
AND a.attencodingtype <> 0)
THEN 1 ELSE 0 END,
100 * CAST(t2.max_blocks_per_slice - t2.min_blocks_per_slice AS FLOAT)
/ CASE WHEN (t2.min_blocks_per_slice = 0)
THEN 1 ELSE t2.min_blocks_per_slice END,
CAST(100 * t2.slice_count AS FLOAT) / (SELECT COUNT(*) FROM STV_SLICES)
FROM temp_staging_tables_1 t1, temp_staging_tables_2 t2
WHERE t1.tableid = t2.tableid;
SELECT * FROM temp_tables_report
ORDER BY schemaname, tablename;
上記の内容をSQLファイルとして保存し、手元の環境からpsqlで接続して-fオプションを使いファイル実行してみます。すると以下の様なフォーマットで結果が得られました。
$ psql -h xxxxxxxx -U yyyy -d zzzzzzzzzzzzzzz -p 5439 -f table_inspector.sql
Password for user root:
psql:table_inspector.sql:2: INFO: Table "temp_staging_tables_1" does not exist and will be skipped
DROP TABLE
psql:table_inspector.sql:3: INFO: Table "temp_staging_tables_2" does not exist and will be skipped
DROP TABLE
psql:table_inspector.sql:4: INFO: Table "temp_tables_report" does not exist and will be skipped
DROP TABLE
CREATE TABLE
INSERT 0 502
CREATE TABLE
INSERT 0 189
CREATE TABLE
INSERT 0 189
schemaname | tablename | tableid | size_in_mb | has_dist_key | has_sort_key | has_col_encoding | pct_skew_across_slices | pct_slices_populated
--------------------+-------------------------------------+---------+------------+--------------+--------------+------------------+------------------------+----------------------
xxxxxxxxxxxx | xxxxxxxxxx_log | 108359 | 14 | 0 | 0 | 0 | 0 | 25
xxxxxxxxxxxx | customer | 108361 | 302 | 0 | 1 | 1 | 0 | 50
xxxxxxxxxxxx | lineorder | 108365 | 25991 | 1 | 1 | 1 | 0.323624595469256 | 100
xxxxxxxxxxxx | xyzxyzxx | 108369 | 118 | 0 | 1 | 1 | 0 | 25
public | earthquake | 108957 | 3434 | 1 | 1 | 1 | 158.318098720293 | 100
public | mlratings | 261336 | 650 | 1 | 1 | 1 | 11.5384615384615 | 100
public | orders_rsutil | 108981 | 266 | 1 | 1 | 1 | 3.07692307692308 | 100
public | orders_test | 108983 | 969 | 1 | 1 | 0 | 10.2222222222222 | 100
public | oooooooooo | 109035 | 329 | 1 | 1 | 1 | 1.21951219512195 | 100
確認すべき項目のそれぞれの"意味"ついては以下の通りです。(※下記ドキュメントの内容を転載させて頂きました)
| レポート項目 | 意味 |
|---|---|
| has_dist_key | テーブルに分散キーが存在するかどうかを示します。 1 はキーが存在することを示し、 0 はキーが存在しないことを示します。 |
| has_sort_key | テーブルにソートキーが存在するかどうかを示します。 1 はキーが存在することを示し、 0 はキーが存在しないことを示します。 |
| has_col_encoding | テーブルのいずれかの列に対して圧縮エンコードが定義されているかどうかを示します。 1 は、少なくとも 1 つの列にエンコードが定義されていることを示します。 0 は、エンコードが定義されていないことを示します。 |
| pct_skew_across_slices | データ分散スキューの割合。値が小さいほど結果は良好です。 |
| pct_slices_populated | 入力されているスライスの割合。値が大きいほど結果は良好です。 |
項目の内容から得られる情報と、対処する方向性の内容としては以下の様な形でしょうか。テーブルの利用状況・他テーブルとの絡み等を考慮し、対処すべきテーブルから着手して行く形となるかと思います。
- has_col_encoding：
- この項目が"0"のテーブルはカラムに対して列圧縮定義が為されていない。よって、設定を行なう事で得られる恩恵に預かれていない可能性が高い。データ件数・ボリュームが多い場合は尚更。偏ったテーブルデータの問題に取り組むのと併せて、適切なテーブル列圧縮定義によるテーブル再作成を検討する必要がありそうです。
- has_dist_key：
- この項目が"0"のテーブルには分散キーが設定されていません。適切な分散キーを設定するべきかも知れませんが、任意のキーを設定する事によって却ってデータの偏りが生まれてしまう可能性があるので、この項目についてはそのまま未設定(EVEN)でも良いのかも知れません。幾つか候補があるのであれば、その候補でそれぞれテーブルを作成してみて比較してみた方が良さそうです。
- has_sort_key：
- この項目が"0"のテーブルにはソートキーが設定されていません。この設定についてはデータのアクセスのされ方、処理のされ方によって設定すべきカラムが変わるので、状況に応じて設定を切り替えて行く必要があります。
- pct_skew_across_slices/pct_slices_populated：
- こちらについてはドキュメントにて言及されている様に、pct_skew_across_slices、pct_slices_populated双方の項目を見ていく必要があります。
テーブル毎のレコードの偏りを見る際のSQLは以下AWS Black Belt Techシリーズ等でも言及されていますが、異なるスキーマで同じテーブル名だった場合の特定が出来なかったので特定する術を踏まえる形で確認してみました。
まずはスキーマ名とテーブル名で対象テーブルのID値を取得。
SELECT
DISTINCT
pg_attribute.attrelid,
pg_stat_user_tables.schemaname,
pg_stat_user_tables.relname
FROM
pg_attribute
INNER JOIN pg_stat_user_tables ON pg_attribute.attrelid = pg_stat_user_tables.relid
WHERE
pg_stat_user_tables.schemaname = 'xxxxxxxxxxxx'
AND pg_stat_user_tables.relname = 'xyzxyzxx';
attrelid | schemaname | relname
----------+--------------+----------
999999 | xxxxxxxxxxxx | xyzxyzxx
従来の取得SQLにID絞り込みの条件を付与して情報ゲット。確認してみたら『偏ってるってレベルじゃねーぞ』な勢いでの偏り具合なテーブルでしたね...(しろめ
このテーブルはデータの偏りがひどい状態となっているので、まずはこの偏りを改善する事でパフォーマンスの向上を期待する事が出来そうです。
SELECT stv_tbl_perm.id, TRIM(stv_tbl_perm.name) AS tablename, stv_tbl_perm.slice, sum(stv_tbl_perm.rows) AS rows FROM stv_tbl_perm WHERE stv_tbl_perm.id = '999999' GROUP BY stv_tbl_perm.id, TRIM(stv_tbl_perm.name), stv_tbl_perm.slice ORDER BY stv_tbl_perm.slice id | tablename | slice | rows --------+-----------+-------+--------- 999999 | xyzxyzxx | 0 | 1000000 999999 | xyzxyzxx | 1 | 0 999999 | xyzxyzxx | 2 | 1000000 999999 | xyzxyzxx | 3 | 0 999999 | xyzxyzxx | 6411 | 0 (5 rows)
[追記]スライスに関する情報として、STV_SLICESというテーブルもあるようです。上記SQLに追記する形でノード・スライスの情報を付与してみました。(※これを見る限り、上記のスライス"6411"のような極端に大きな数字についてはリーダーノードの情報になるのでは、と推測します)
SELECT stv_tbl_perm.id, TRIM(stv_tbl_perm.name) AS tablename, stv_slices.node, stv_tbl_perm.slice, sum(stv_tbl_perm.rows) AS rows FROM stv_tbl_perm INNER JOIN stv_slices ON stv_tbl_perm.slice = stv_slices.slice WHERE stv_tbl_perm.id = '999999' GROUP BY stv_tbl_perm.id, TRIM(stv_tbl_perm.name), stv_slices.node, stv_tbl_perm.slice ORDER BY stv_slices.node, stv_tbl_perm.slice; id | tablename | node | slice | rows --------+-------------+------+-------+--------- 999999 | xxxxxxxxxxx | 0 | 0 | 1513883 999999 | xxxxxxxxxxx | 0 | 1 | 1513883 999999 | xxxxxxxxxxx | 1 | 2 | 1513882 999999 | xxxxxxxxxxx | 1 | 3 | 1513882 (4 rows)
まとめ
以上、『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』トピック2つめ、"偏ったテーブルデータ"に関する対処方法のご紹介でした。3つ目以降のトピックについても、こんな感じで読み解きつつ実践して行きたいと思います。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
